You are here:

Custom Members

The Pyramid application provides the ability for end users to define and use custom measures or members in the application. A custom member or measure is a user defined calculation, written in MDX that can be used to derive values anywhere within the query. To help users define custom calculations, the application comes with a Custom Member/Measure MDX editor complete with cube meta data structures, MDX function library and script formatting capabilities.

Professional and Analyst users, with no extended security limitations, can define and use custom members like any other element in the cube. Consumer users can use custom members defined by the other user types only.

Custom Member definitions can be written with any form of MDX that is compliant with Microsoft's 2005/2008/2012 MDX standards. A Custom Measure Wizard is provided for end users to help define basic metrics and is exclusively for Custom Measures definitions (members that are defined for the measure dimension).

The main MDX Editor and Custom Set editors are similar to the Custom Member calculation editor, but expose different features.

NOTE: This manual includes a small reference library for all the MDX functions available. However, it is beyond the scope of this manual to provide an in-depth reference. Other third party sources should be used for more detail. See the MDX Function Listing for more detail.

Accessing the Custom Members Editor

The Custom Members Editor can be invoked from a variety of places by Professional and Analyst users :

The query ribbon Custom MDX drop down menu
The shortcut menu in the element tree.
The right-click context menu within the element tree.
- If users select elements within the element tree and choose to create a custom member, the application will auto-fill the query window with your selection
- This also exposes a menu driven wizard for building a variety of pre-defined custom member calculations.

Using & Maintaining Custom Members

Once created, custom members will appear at the bottom of the relevant hierarchy's element tree in the Custom Elements folder. From here, custom members can be used in queries like any other element in the hierarchy.
To Edit a custom member, users can right click on the custom member and choose "edit custom member" from the context menu.
Users can Delete custom members if the custom element if NOT being used in a report, another custom member or custom set. Warnings will be provided in the case of conflicts.

Custom Member Editor Window

The Custom Member Editor window exposes 4 main sections:

A format bar (highlighted in green) at the top which allows users to choose the font used for the query window
The query window that contains the query text (highlighted in red)
The Meta data and function library (highlighted in orange) which exposes the entire cube structure and the full MDX function library
Custom Member property settings (highlighted in blue) which exposes a variety of options for defining the custom member.

Initially, the custom member properties are visible while the meta data section is collapsed. Click the expander buttons (blue and red arrows) to show/hide these sections. Once the meta data section is exposed, users can drag and drop items from each of the 3 panels into the query window.

Items dragged from the hierarchy (left) or element (middle) trees will result in the UNIQUENAME of the selected name being added to the query window. This includes dimensions, hierarchies, levels and member elements.
Items dragger from the function list (right) will result in the functions GENERIC FORMULATION being dropped into the query window.

Other features

To choose a different element list in the middle, double click the relevant hierarchy in the left hand panel.
Hovering over elements in the middle panel will highlight the element's tool-tips and its uniquename.
Hovering over the functions will show its formulation as a tool-tip
Double clicking elements in the middle panel or functions in the right panel will insert them into the query window at the current caret position

Once a custom member calculation has been defined, users can click the TEST MDX button to check their syntax and the CREATE button to commit their logic to the server.

Formulating a Custom Member

In defining a custom member, users are effectively creating a statement that will be executed in the MDX engine with the following standard syntax:

MEMBER [dimension].[hierarchy].[parent name].[Custom Member Name] as formulation, properties

The calculation definition added into the query window is the "formulation" aspect of the above statement, with the properties of the custom member following the syntax. To enable this functionality, the following items should be in place:

All Custom members must have a name (top left hand corner) and can optionally be given a description.
Each custom member must have a designated parent dimension and hierarchy (in the properties section on the left).Select these from the drop down lists.
Each custom member can OPTIONALLY have a parent member. This is will place the custom member as a CHILD in the hierarchical tree under this parent element. If not parent element is provided, the custom member is shown as the top level of the tree.
A format string can be applied to custom members that will determine how metrics for this custom element are formatted. Users can select from a list of presets or provide their own custom formats.
1. Format string options include a variety of presets - like currency, percentage and standard number masks like "#,#".
2. You can also supply a custom format. Custom format masks need to conform to the standards described here here.
3. Last, you can supply a custom formulation which is a formula driven string that will produce a format mask based on mathematical or query driven inputs.
A solve order can be applied to the custom element, determining the order in which the calculated member will be solved in cases where a calculated member references one other calculated member (that is, where calculated members intersect each other).
Isolation Mode sets the scope isolation of the custom member. See Scope Isolation below for more detail.
The non empty behavior property only applies to custom MEASURES and is set to an existing measure that is used to determine the behavior of calculated members when resolving empty cells.
Foreground and Background colors: these properties allow users to format their calculated members so that they appear with colors in the grid. These MDX driven formatting options can be used with complex logic to deliver cube defined conditional formatting amongst other things.
Multi-mode is a special Pyramid-only flag for the application of parameterized custom member calculations.

Scope Isolation

When an MDX script contains calculated members, by default the calculated members are resolved before any session-scoped calculations are resolved and before any query-defined calculations are resolved. The behavior allows client applications to work with cubes that contain complex calculations, without having to take into account the specific implementation of the calculations. However, in certain scenarios, you might want to execute session or query-scoped calculated members before certain calculations in the cube. To accomplish this, set the isolation mode property to "cube" .

Custom Member Accessibility

All custom elements (members or set) can be secured.

Private access ensures that the definition is only available to the user that created it. If a private calculation is used in public content, other users will be able to run queries using the item, but they CANNOT change or edit its definition.
Public access allows any user with access to this cube and READ access to the element's definition to consume the definition. If the user also has write access, they can edit and update the definition as well.